package com.tsd.core.excel.export;

import com.tsd.core.utils.HlpUtils;
import com.tsd.core.vo.HlpException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @author Hillpool
 */
public class ExcelExportHelper {
    // 设置cell编码解决中文高位字节截断
    // private static short XLS_ENCODING = HSSFWorkbook.ENCODING_UTF_16;
    public static final int MAX_ROW = 1000;

    private Workbook workbook;
    private final DataFormat dataFormat;

    private final List<Sheet> sheets = new ArrayList<Sheet>(1);

    private Sheet sheet;

    private Row row;

    private CellStyles cellStyles;
    /**
     * 根据样例行的cell style
     */
    private List<CellStyle> cellStyleList;

    private int rowIndex = 0;

    private FormulaEvaluator formulaEvaluator;

    public enum ExcelType {
        XLS("xls"), XLSX("xlsx");

        private final String type;

        ExcelType(String type) {
            this.type = type;
        }

        public String getType() {
            return this.type;
        }
    }

    public ExcelExportHelper(int intFirstDataRow) {
        //默认创建xlsx:ExcelExportHelper.ExcelType.XLSX
        int rowIndex = intFirstDataRow > 0 ? intFirstDataRow - 1 : 0;
        initDefaultExcel();
        if (rowIndex > 0) {
            for (int i = 0; i < rowIndex; i++) {
                this.createRow();
            }
        }
        this.dataFormat = this.workbook.createDataFormat();
    }

    private void initDefaultExcel() {
        this.workbook = new XSSFWorkbook();
        this.sheet = workbook.createSheet();
        this.sheets.add(sheet);
        this.formulaEvaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
    }

    public ExcelExportHelper(ExcelType excelType) {
        if (Objects.requireNonNull(excelType) == ExcelType.XLS) {
            this.workbook = new HSSFWorkbook();
        } else {
            this.workbook = new XSSFWorkbook();
        }
        this.sheet = workbook.createSheet();
        this.sheets.add(sheet);
        this.formulaEvaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
        this.dataFormat = this.workbook.createDataFormat();
    }

    public ExcelExportHelper(File file, int intFirstDataRow) throws IOException {
        int rowIndex = intFirstDataRow > 0 ? intFirstDataRow - 1 : 0;
        if (HlpUtils.isEmpty(file)) {
            initDefaultExcel();
            this.dataFormat = this.workbook.createDataFormat();
            if (rowIndex > 0) {
                for (int i = 0; i < rowIndex; i++) {
                    this.createRow();
                }
            }
        } else {
            InputStream input = Files.newInputStream(file.toPath());
            String fileName = file.getName();
            int end = fileName.lastIndexOf(".");
            String ext = fileName.substring(end + 1);
            if (ext.equalsIgnoreCase(ExcelType.XLS.getType())) {
                POIFSFileSystem fs = new POIFSFileSystem(input);
                this.workbook = new HSSFWorkbook(fs);
            } else if (ext.equalsIgnoreCase(ExcelType.XLSX.getType())) {
                this.workbook = new XSSFWorkbook(input);
            }
            this.dataFormat = this.workbook.createDataFormat();
            this.sheet = workbook.getSheetAt(0);
            if (rowIndex > 0) {
                for (int i = 0; i < rowIndex; i++) {
                    this.createRow();
                }
            }
            initStyle(this.rowIndex);
        }
        this.formulaEvaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
    }

    public Font createNormalFont() {
        Font normalFont = this.workbook.createFont();
        normalFont.setFontHeightInPoints((short) 10);
        normalFont.setFontName("宋体");
        return normalFont;
    }

    public Font createBoldFont() {
        Font boldFont = this.workbook.createFont();
        boldFont.setFontHeightInPoints((short) 10);
        boldFont.setBold(true);
        boldFont.setFontName("宋体");
        return boldFont;
    }

    public Font createBoldTitleFont() {
        Font boldTitleFont = this.workbook.createFont();
        boldTitleFont.setFontHeightInPoints((short) 14);
        boldTitleFont.setBold(true);
        boldTitleFont.setFontName("宋体");
        return boldTitleFont;
    }

    public Font createTotalFont() {
        Font totalFont = this.workbook.createFont();
        totalFont.setFontHeightInPoints((short) 10);
        totalFont.setBold(true);
        totalFont.setFontName("宋体");
        return totalFont;
    }

    private void setBorder(CellStyle style) {
        // 设置边框
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());

        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
    }

    public CellStyles createCellStyles() {
        return new DefaultCellStyles(this.getWorkbook());
    }

    public CellStyle createHeaderStyle() {
        return createHeaderStyle(null);
    }

    public CellStyle createHeaderStyle(Cell cell) {
        CellStyle headerStyle;
        if (cell != null) {
            headerStyle = cell.getCellStyle();
        } else {
            headerStyle = this.workbook.createCellStyle();
        }
        headerStyle.setFont(this.createNormalFont());
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        this.setBorder(headerStyle);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return headerStyle;
    }

    public CellStyle createBoldTitleStyle() {
        return createBoldTitleStyle(null);
    }

    public CellStyle createBoldTitleStyle(Cell cell) {
        CellStyle boldTitleStyle;
        if (cell != null) {
            boldTitleStyle = cell.getCellStyle();
        } else {
            boldTitleStyle = this.workbook.createCellStyle();
        }
        boldTitleStyle.setFont(this.createBoldTitleFont());
        boldTitleStyle.setAlignment(HorizontalAlignment.CENTER);
        boldTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        this.setBorder(boldTitleStyle);
        boldTitleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        boldTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return boldTitleStyle;
    }

    public CellStyle createStringStyle() {
        return createStringStyle(null);
    }

    public CellStyle createStringStyle(Cell cell) {
        CellStyle stringStyle;
        if (cell != null) {
            stringStyle = cell.getCellStyle();
        } else {
            stringStyle = this.workbook.createCellStyle();
        }
        stringStyle.setFont(this.createNormalFont());
        this.setBorder(stringStyle);
        stringStyle.setDataFormat(this.dataFormat.getFormat(DefaultCellStyles.ExcelFormat.STRING.getPattern()));
        return stringStyle;
    }

    public CellStyle createIntegerStyle() {
        return createIntegerStyle(null);
    }

    public CellStyle createIntegerStyle(Cell cell) {
        CellStyle integerCellStyle;
        if (cell != null) {
            integerCellStyle = cell.getCellStyle();
        } else {
            integerCellStyle = this.workbook.createCellStyle();
        }
        integerCellStyle.setFont(this.createNormalFont());
        this.setBorder(integerCellStyle);
        integerCellStyle.setDataFormat(this.dataFormat.getFormat(DefaultCellStyles.ExcelFormat.INTEGER.getPattern()));
        return integerCellStyle;
    }

    public CellStyle createCurrencyStyle() {
        return createCurrencyStyle(null);
    }

    public CellStyle createCurrencyStyle(Cell cell) {
        CellStyle currencyCellStyle;
        if (cell != null) {
            currencyCellStyle = cell.getCellStyle();
        } else {
            currencyCellStyle = this.workbook.createCellStyle();
        }
        currencyCellStyle.setFont(this.createNormalFont());
        currencyCellStyle.setDataFormat(this.dataFormat.getFormat(DefaultCellStyles.ExcelFormat.CURRENCY.getPattern()));
        this.setBorder(currencyCellStyle);
        return currencyCellStyle;
    }

    public CellStyle createDateStyle() {
        return createDateStyle(null);
    }

    public CellStyle createDateStyle(Cell cell) {
        CellStyle dateCellStyle;
        if (cell != null) {
            dateCellStyle = cell.getCellStyle();
        } else {
            dateCellStyle = this.workbook.createCellStyle();
        }
        dateCellStyle.setFont(this.createNormalFont());
        dateCellStyle.setDataFormat(this.dataFormat.getFormat(DefaultCellStyles.ExcelFormat.DATETIME.getPattern()));
        this.setBorder(dateCellStyle);
        return dateCellStyle;
    }

    public CellStyle createFormulaStyle() {
        return createFormulaStyle(null);
    }

    public CellStyle createFormulaStyle(Cell cell) {
        CellStyle totalStyle;
        if (cell != null) {
            totalStyle = cell.getCellStyle();
        } else {
            totalStyle = this.workbook.createCellStyle();
        }
        totalStyle.setFont(this.createTotalFont());
        totalStyle.setWrapText(true);
        totalStyle.setAlignment(HorizontalAlignment.CENTER);
        this.setBorder(totalStyle);
        return totalStyle;
    }

    public CellStyle createNumberStyle() {
        return createNumberStyle(null);
    }

    public CellStyle createNumberStyle(Cell cell) {
        CellStyle numberCellStyle;
        if (cell != null) {
            numberCellStyle = cell.getCellStyle();
        } else {
            numberCellStyle = this.workbook.createCellStyle();
        }
        numberCellStyle.setFont(this.createNormalFont());
        numberCellStyle.setDataFormat(this.dataFormat.getFormat(DefaultCellStyles.ExcelFormat.NUMBER.getPattern()));
        this.setBorder(numberCellStyle);
        return numberCellStyle;
    }

    //根据某行获取各个cell的style
    private void initStyle(int rowIndex) {
        Row row = sheet.getRow(rowIndex);
        int cellCount = row.getLastCellNum() - row.getFirstCellNum();
        cellStyleList = new ArrayList<>();
        if (cellCount > 0) {
            for (int i = 0; i < cellCount; i++) {
                cellStyleList.add(this.createStringStyle());
            }
        }
    }

    /**
     * 导出Excel文件
     *
     * @throws HlpException
     */
    public void export(String xlsFileName) throws HlpException {
        try {
            export(new FileOutputStream(xlsFileName));
        } catch (FileNotFoundException e) {
            throw new HlpException("生成导出Excel文件出错：文件不存在");
        }
    }

    public void export(OutputStream os) throws RuntimeException {
        try {
            workbook.setForceFormulaRecalculation(true);
            workbook.write(os);
            os.flush();
        } catch (FileNotFoundException e) {
            throw new RuntimeException("生成导出Excel文件出错!", e);
        } catch (IOException e) {
            throw new RuntimeException("写入Excel文件出错!", e);
        } finally {
            try {
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public void setCell(int index, String value, CellStyle cellStyle) {
        Cell cell = this.row.getCell(index);
        if (cell == null) {
            cell = this.row.createCell(index);
        }
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCell(int index, String value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    public void setCell(int index, Date value, CellStyle cellStyle) {
        Cell cell = this.row.getCell(index);
        if (cell == null) {
            cell = this.row.createCell(index);
        }
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCell(int index, Date value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getDateStyle());
        } else {
            setCell(index, value, null);
        }
    }

    public void setCell(int index, int value, CellStyle cellStyle) {
        Cell cell = this.row.getCell(index);
        if (cell == null) {
            cell = this.row.createCell(index);
        }
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCell(int index, int value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    public void setCell(int index, double value, CellStyle cellStyle) {
        Cell cell = this.row.getCell(index);
        if (cell == null) {
            cell = this.row.createCell(index);
        }
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCell(int index, double value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getNumberStyle());
        } else {
            setCell(index, value, null);
        }
    }

    public void setCell(int index, float value, CellStyle cellStyle) {
        Cell cell = this.row.getCell(index);
        if (cell == null) {
            cell = this.row.createCell(index);
        }
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCell(int index, float value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getNumberStyle());
        } else {
            setCell(index, value, null);
        }
    }

    public void setCellHeader(int rowIndex, int cellIndex, String value, CellStyle cellStyle) {
        this.setCellHeader(rowIndex, cellIndex, value, cellStyle, null);
    }

    public void setCellHeader(int rowIndex, int cellIndex, String value, XSSFCellStyle cellStyle) {
        this.setCellHeader(rowIndex, cellIndex, value, cellStyle, null);
    }

    public void setCellHeader(int rowIndex, int cellIndex, String value, CellStyle cellStyle, Integer width) {
        this.row = this.sheet.getRow(rowIndex);
        if (this.row == null) {
            this.createHeaderRow();
        }
        this.row.setHeightInPoints(25F);
        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = this.row.createCell(cellIndex);
        }
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
        if (width != null) {
            this.sheet.setColumnWidth(cellIndex, width);
        }
    }

    public void setCellHeader(int rowIndex, int cellIndex, String value, XSSFCellStyle cellStyle, Integer width) {
        this.row = this.sheet.getRow(rowIndex);
        if (this.row == null) {
            this.createHeaderRow();
        }
        this.row.setHeightInPoints(25F);
        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = this.row.createCell(cellIndex);
        }
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
        if (width != null) {
            this.sheet.setColumnWidth(cellIndex, width);
        }
    }

    public void setCellHeader(int index, String value, CellStyle cellStyle) {
        Cell cell = this.row.createCell(index);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCellHeader(int index, String value) {
        if (cellStyles != null) {
            setCellHeader(index, value, cellStyles.getHeaderStyle());
        } else {
            setCell(index, value, null);
        }
    }

    public void setCellFormula(int index, String formula, CellStyle cellStyle) {
        Cell cell = this.row.createCell(index);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellFormula(formula);
        cell.setCellValue("");
        this.workbook.setForceFormulaRecalculation(true);
        this.formulaEvaluator.evaluateFormulaCell(cell);
    }

    public void setCellFormula(int index, String formula) {
        if (cellStyles != null) {
            setCellFormula(index, formula, cellStyles.getFormulaStyle());
        } else {
            setCell(index, formula, null);
        }
    }

    /**
     * 增加一行
     */
    public void createRow() {
        // 超过MAX_ROW自动创建信的sheet
        /*
         * if (getRowIndex() > MAX_ROW) { this.sheet = workbook.createSheet();
         * this.sheets.add(sheet);
         *
         * rowIndex = 0; }
         */
        this.row = this.sheet.createRow(rowIndex++);
        this.row.setHeightInPoints(20F);
    }

    /**
     * 增加一行
     */
    public void createHeaderRow() {
        this.row = this.sheet.createRow(rowIndex++);
        this.row.setHeightInPoints(25F);
    }

    public void createSheet() {
        this.sheet = workbook.createSheet();
        this.sheets.add(sheet);
        rowIndex = 0;
    }

    /**
     * 增加一行
     *
     * @param headers
     */
    public void createRow(List<String> headers) {
        // 超过MAX_ROW自动创建信的sheet
        if (getRowIndex() > MAX_ROW) {
            this.sheet = workbook.createSheet();
            this.sheets.add(sheet);
            rowIndex = 0;
        }
        this.row = this.sheet.createRow(rowIndex++);
        if (rowIndex == 1) {
            for (int i = 0; i < headers.size(); i++) {
                this.setCellHeader(i, headers.get(i));
            }
        }
    }

    public String toRadix(Integer num) {
        String[] letters = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
                "S", "T", "U", "V", "W", "X", "Y", "Z"};
        StringBuilder columnStr = new StringBuilder();
        while (true) {
            int remain = num % 26;
            columnStr.insert(0, letters[remain]);
            num = num / 26;
            if (num == 0) {
                break;
            }
            num--;
        }
        return columnStr.toString();
    }

    /**
     * 返回 workbook 的值
     *
     * @return workbook
     */
    public Workbook getWorkbook() {
        return workbook;
    }

    /**
     * 返回 sheet 的值
     *
     * @return sheet
     */
    public Sheet getSheet() {
        return sheet;
    }

    /**
     * 返回 row 的值
     *
     * @return row
     */
    public Row getRow() {
        return row;
    }

    public void setRow(Row row) {
        this.row = row;
    }

    /**
     * 返回 rowIndex 的值
     *
     * @return rowIndex
     */
    public int getRowIndex() {
        return rowIndex;
    }

    public void setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
    }

    public CellStyles getCellStyles() {
        return cellStyles;
    }

    public void setCellStyles(CellStyles cellStyles) {
        this.cellStyles = cellStyles;
    }

    public List<Sheet> getSheets() {
        return sheets;
    }

    public FormulaEvaluator getFormulaEvaluator() {
        return formulaEvaluator;
    }

    public void setFormulaEvaluator(FormulaEvaluator formulaEvaluator) {
        this.formulaEvaluator = formulaEvaluator;
    }

    /**
     * 根据cell索引获取cellstyle
     *
     * @param cellIndex
     * @return
     */
    public CellStyle getCellStyle(int cellIndex) {
        if (cellStyleList != null && cellIndex > -1 && cellIndex < cellStyleList.size()) {
            return cellStyleList.get(cellIndex);
        }
        return null;
    }

    public void addMergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
        this.getSheet().addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * rgb转int
     */
    private int getIntFromColor(int red, int green, int blue) {
        red = (red << 16) & 0x00FF0000;
        green = (green << 8) & 0x0000FF00;
        blue = blue & 0x000000FF;
        return 0xFF000000 | red | green | blue;
    }

    /**
     * int转byte[]
     */
    private byte[] intToByteArray(int i) {
        byte[] result = new byte[4];
        result[0] = (byte) ((i >> 24) & 0xFF);
        result[1] = (byte) ((i >> 16) & 0xFF);
        result[2] = (byte) ((i >> 8) & 0xFF);
        result[3] = (byte) (i & 0xFF);
        return result;
    }

    public XSSFColor getColor(int red, int green, int blue) {
        //自定义颜色对象
        XSSFColor color = new XSSFColor(new DefaultIndexedColorMap());
        //根据你需要的rgb值获取byte数组
        color.setRGB(intToByteArray(getIntFromColor(red, green, blue)));
        return color;
    }

    public Cell getCell(int rowIndex, int cellIndex) {
        Row sheetRow = this.sheet.getRow(rowIndex);
        if (sheetRow == null) {
            sheetRow = this.sheet.createRow(rowIndex);
            sheetRow.setHeightInPoints(20F);
        }
        Cell rowCell = sheetRow.getCell(cellIndex);
        if (rowCell == null) {
            rowCell = sheetRow.createCell(cellIndex);
        }
        return rowCell;
    }
}
